package com.F03.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F03.info.PdPlanningInfo;
import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.util.CommonTools;

public class PdFinishProductDAO extends DataOperations{

	Logger log = Logger.getLogger(PdPlanningDAO.class);
	public PdFinishProductDAO(){
		
	}
    public PdFinishProductDAO(Connection connection){
        super(connection);
    }
    
    public List queryTotalList(PdPlanningInfo info) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	
    	sql.append(" select sum(a.qty) as group_qty,sum(convert(float,d.unit_price)) as total_unit_price,sum(d.unit_price*a.qty) as total_total_price ");
    	sql.append(" from pd_finish_product a,c_orders d ");	
    	sql.append(" where  a.manufacturing_order = d.manufacturing_order ");
    	
    	if(!info.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+info.getManufacturingOrder1()+"' ");
    	}
    	if(!info.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+info.getManufacturingOrder2()+"' ");
    	}
    	if(!info.getLastAdvanceDate1().equals("")){
    		sql.append(" and a.stock_date >=convert(datetime,'"+info.getLastAdvanceDate1()+"')");
    	}
    	if(!info.getLastAdvanceDate2().equals("")){
    		sql.append(" and a.stock_date <=convert(datetime,'"+info.getLastAdvanceDate2()+"')");
    	}
       
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
				map.put("totalGroupQty", CommonTools.null2String(rest.getString("group_qty")));
				map.put("totalUnitPrice", CommonTools.convertCurrencyRMB(rest.getString("total_unit_price")));
				map.put("totalTotalPrice", CommonTools.convertCurrencyRMB(rest.getString("total_total_price")));

				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
    public List queryFinishProductList(PdPlanningInfo info) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	String manufacturingOrderCycle = "";
    	String manufacturingOrder="";
    	String model = "";
    	String finishQty = "";
    	sql.append(" select a.manufacturing_order,b.name,adyh.FUN_GETFINISHQTY(a.manufacturing_order) as finish_qty, ");
    	sql.append(" a.groups,a.qty,a.stock_date,d.unit_price, ");
    	sql.append(" (d.unit_price*a.qty) as total_price ");
    	sql.append(" from pd_finish_product a, t_product_model b,t_not_standard_products c,c_orders d ");
    	sql.append("  where a.manufacturing_order = c.manufacturing_order and  ");
    	sql.append(" c.model_type = b.code and a.manufacturing_order = d.manufacturing_order ");
    	if(!info.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+info.getManufacturingOrder1()+"' ");
    	}
    	if(!info.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+info.getManufacturingOrder2()+"' ");
    	}
    	if(!info.getLastAdvanceDate1().equals("")){
    		sql.append(" and a.stock_date >=convert(datetime,'"+info.getLastAdvanceDate1()+"')");
    	}
    	if(!info.getLastAdvanceDate2().equals("")){
    		sql.append(" and a.stock_date <=convert(datetime,'"+info.getLastAdvanceDate2()+"')");
    	}
        sql.append(" order by a.manufacturing_order ");
        
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			if(manufacturingOrderCycle.equals(CommonTools.null2String(rest.getString("manufacturing_order")))){
    				manufacturingOrder ="";
    				model ="";
    				finishQty ="";
    			}else{
    				manufacturingOrderCycle = CommonTools.null2String(rest.getString("manufacturing_order"));
    				manufacturingOrder = CommonTools.null2String(rest.getString("manufacturing_order"));
    				model = CommonTools.null2String(rest.getString("name"));
    				finishQty =CommonTools.null2String(rest.getString("finish_qty"));
    			}
    		
				map.put("manufacturingOrder", manufacturingOrder);
				map.put("model", model);
				map.put("finishQty", finishQty);
				map.put("groups", CommonTools.null2String(rest.getString("groups")));
				map.put("groupQty", CommonTools.null2String(rest.getString("qty")));
				map.put("stockDate", CommonTools.getDateTime(rest.getDate("stock_date")));
				map.put("unitPrice", CommonTools.convertCurrencyRMB(rest.getString("unit_price")));
				map.put("totalPrice", CommonTools.convertCurrencyRMB(rest.getString("total_price")));
				
				list.add(map);
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
}
